Hi!

I'm new to MDX and have a simple problem with apparently no simple solution in MDX word, according to discussions, e.g. http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_thread/thread/25ea010035f3f097/8ab30ca61c23bbfd%238ab30ca61c23bbfd

Is it possible toget records from OLAP cube, usingOR condition to members of different dimensions?Below is an abstract example using [Adventure Works]:

select
non empty {[Measures].[Reseller Sales Amount]} on columns,
non empty { [Employee].[Employee].AllMembers
* [Product].[Product Line].AllMembers } on rows
from [Adventure Works]
where
[Geography].[Country].&[Canada]
OR SOMEHOW
[Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop]

Records should be returned if either country is CanadaOR Business Type is Bike Shop. It's trivial with regular SQL, but seems to be very tricky with MDX. There may be more than 2 parameters.
It is easy to implement OR condition if parameters belong to the same dimension. But how if they are different?

Thanks!
Andy.

It is easy to do as well. Depending on how calculations are inside the cube it might be done in couple of different ways, but below is probably what most people would write here.

select

non empty {[Measures].[Reseller Sales Amount]} on columns,

non empty { [Employee].[Employee].AllMembers

* [Product].[Product Line].AllMembers } on rows

from [Adventure Works]

where

{([Geography].[Country].&[Canada], [Reseller].[Reseller Type].[Business Type].MEMBERS),

([Geography].[Country].MEMBERS,[Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop])}

How would you implement this if you had the following situation? (i am just using pseudocode and made up dimensions)?

WHERE

(
{[Dim X].&[1], [Dim X].&[2]}

OR

{[Dim Z].&[1],[Dim Z].&[2]}

OR

[Dim A].&[4]

)

....

You would approach this the same as the previous examples. You would just need to crossjoin the 3 dimensions together (in the same order each time) and union the results. The * operator does a crossjoin and the + does a union.

WHERE

{ {[Dim X].&[1], [Dim X].&[2]} *[Dim Z].[Dim Z].[All] *[Dim A].[Dim A].[All] }

+

{ [Dim X].[Dim X].[All] *{[Dim Z].&[1],[Dim Z].&[2]} * [Dim A].[Dim A].[All] }

+

{ [Dim X].[Dim X].[All] * [Dim Z].[Dim Z].[All] * [Dim A].&[4] }

Thanks Darren for this prompt reply and for your many useful posts online.. i am a follower of your blog too!


i actually went ahead and implemented the following which i believe is giving me the correct results (i understand the named sets are not necessary but they are reused often elsehwere and provide better readbility in the MDX). My named sets are SSAS2008 static sets created in the cube:



WHERE

{

([Named set for members in Dim X], [Dim Z].[Dim Z].members, [Dim A].[Dim A].members)

,

([Dim X].[Dim X].members, [Named set for members in Dim Z], [Dim A].[Dim A].members)

,

([Dim X].[Dim X].members, [Dim Z].[Dim Z].members, [Dim A].&[4])

}


the above was actually also put into the cube as a static set (lets call it [Large Set] )


Some questions:

1. does my approach appear correct?
2. does the use of .members VS .[All] incur a performance penalty?
3. Is the set {[Dim X].&[1], [Dim X].&[2]} *[Dim Z].[Dim Z].[All] *[Dim A].[Dim A].[All] } the same as the tuple ([Named set for Dim X], [Dim Z].[Dim Z].[All], [Dim A].[Dim A].[All]) ? (I believe a measure gives the same result when sliced with these alternate methods)
4. Would it be better to create these as dynamic sets for cases where i would be writing WHERE ([Large Set], [Time].&[Something], [x].&[1]) or similar?

Some questions:

1. does my approach appear correct?
2. does the use of .members VS .[All] incur a performance penalty?
3. Is the set {[Dim X].&[1], [Dim X].&[2]} *[Dim Z].[Dim Z].[All] *[Dim A].[Dim A].[All] } the same as the tuple ([Named set for Dim X], [Dim Z].[Dim Z].[All], [Dim A].[Dim A].[All]) ? (I believe a measure gives the same result when sliced with these alternate methods)
4. Would it be better to create these as dynamic sets for cases where i would be writing WHERE ([Large Set], [Time].&[Something], [x].&[1]) or similar?

Thanks for the feedback, it's nice to know that what I am doing is helping people. :)

1. Yes, your approach appears correct.
2. I checked with Mosha and there shouldn't be any performance difference between All vs .MEMBERS when used in the WHERE clause
3. Yes, using named sets or inline setswill produce the same output. Although there are some circumstances where named don't get optimized the same so your performance may vary.
4. No, dynamic sets will not make any difference to the logic in this case. Dynamic sets get re-evaluated in the context of the WHERE clause, so it's only when they are on a visible axis likethe rows or columns that they are dynamic. When they are in the WHERE clause they are effectively static.

many thanks, glad i am on the right track!

some one please suggest me how to implement OR condition in following situation.

SELECT NON EMPTY{ [Measures].[PREM ] } ON COLUMNS ,

NON EMPTY { ([PREFIX].[PREFIX].[PREFIX].ALLMEMBERS ) }ON ROWS

FROM(
SELECT

( [TXN EFF DATE].[TXN EFF DT].&[2011-05-01T00:00:00] :

[TXN EFF DATE].[TXN EFF DT].&[2011-05-31T00:00:00] )ON COLUMNS

FROM (

SELECT
( [ACCOUNT DATE].[ACCT DT].&[2010-10-01T00:00:00] :

[ACCOUNT DATE].[ACCT DT].&[2011-05-01T00:00:00] )ON COLUMNS

 I NEED OR CONDITION HERE

 
//FROM

// (SELECT

// ( [TXN EFF DATE].[TXN EFF DT].&[2008-12-19T00:00:00] :

// [TXN EFF DATE].[TXN EFF DT].&[2011-05-30T00:00:00] ) ON COLUMNS

// FROM

// ( SELECT ( [ACCOUNT DATE].[ACCT DT].&[2010-10-01T00:00:00] :

// [ACCOUNT DATE].[ACCT DT].&[2011-05-01T00:00:00] ) ON COLUMNS

 FROM [POLICY]))

In Sql i have condition like this....

(dim.ACCT_DT >= '2011-05-01' And ACCT_DT < '2011-06-01'
        And TXN_EFF_DT < '2011-05-31'
       
       or
        TXN_EFF_DT >= '2011-05-01' And TXN_EFF_DT < '2011-06-01'
        and
        ACCT_DT < '2011-06-01')

You would do this using the same technique as previously described in this thread of crossjoining with the All member. This sort of filter condition is also known as a Asymmetric Set and you can use a .Net function to generate them as demonstrated here http://asstoredprocedures.codeplex.com/wikipage?title=AsymmetricSet&referringTitle=Home

SELECT NON EMPTY{ [Measures].[PREM ] } ON COLUMNS ,

NON EMPTY { ([PREFIX].[PREFIX].[PREFIX].ALLMEMBERS ) }ON ROWS

FROM(
SELECT

( [TXN EFF DATE].[TXN EFF DT].&[2011-05-01T00:00:00] :

[TXN EFF DATE].[TXN EFF DT].&[2011-05-31T00:00:00] ) * [ACCOUNT DATE].[ACCT DT].[All] ON COLUMNS

FROM (

SELECT
( [ACCOUNT DATE].[ACCT DT].&[2010-10-01T00:00:00] :

[ACCOUNT DATE].[ACCT DT].&[2011-05-01T00:00:00] ) * [TXN EFF DATE].[TXN EFF DT] ON COLUMNS ) )

PS. it's generally better to ask a new question in a new thread rather than replying to a previously answered question. It's great to search for a possible answer and include links to questions that you think may help, but people that are looking to answer questions will generally not bother reading a thread that is already marked as answered.

Hi all,

i have a similar problem, I'm trying to make an OR condition in the WHERE clause (doesn't necessarily as to be there...)

but im getting the fact that i can't use more then one member in the same hierarchy.

do you have any suggestions?

here is the script - I left the OR so i can let you know what I need:

select non empty {[Comm Date UTC].[Dates].[Date].members} on 1 ,

{[Measures].[Number of Answered Comms],measures.[Six_Weeks_Average]} on 0
  FROM ( SELECT ( 
 
 [Comm Date UTC].[Dates].[Date].&[20140110]:[Comm Date UTC].[Dates].[Date].&[20150201]
     , [Service Comm Type].[Content Type].&[1]--calls
 
      ) ON COLUMNS FROM [Comms] ) 
  where
  ([Route Class].[Route Class].&[54],[SourceUserType].[UserTypeID].&[8])
  or
  ([Route Class].[Route Class].&[45],[DestUserType].[UserTypeID].&[8])
  or 
  (([Route Class].[Route Class].&[55],[DestUserType].[UserTypeID].&[8]) or ([Route Class].[Route Class].&[55],[SourceUserType].[UserTypeID].&[8]) )

thank you!

Yoni